/*
 * Copyright(C) 2012 Luvina Software Company
 *
 * TblUserDaoImpl.java, May 28, 2012 HaiPD
 */
package net.luvina.manageuser.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import net.luvina.manageuser.dao.TblUserDao;
import net.luvina.manageuser.dto.UserDetailDto;
import net.luvina.manageuser.hibernate.TblUser;

/**
 * TblUserDaoImpl implements TblUserDao
 *
 * @author HaiPD
 *
 */
@Repository("userDao")
public class TblUserDaoImpl extends GenericDaoImpl<TblUser, Integer> implements
		TblUserDao {

	public TblUserDaoImpl() {
		super(TblUser.class);
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see net.luvina.manageuser.dao.TblUserDao#getListUser(int, int, int,
	 * java.lang.String, java.lang.String, java.lang.String, java.lang.String,
	 * java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	public List<UserDetailDto> getListUser(final int offset, final int limit,
			final int groupId, final String fullName, final String sortType,
			final String sortByFullName, final String sortByCodeLevel,
			final String sortByEndDate) {

		HibernateCallback callback = new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuilder sqlCommand = new StringBuilder("");
				sqlCommand = sqlCommand.append("SELECT ");
				sqlCommand = sqlCommand.append("u.user_id as userId, ");
				sqlCommand = sqlCommand.append("u.full_name as fullName, ");
				sqlCommand = sqlCommand.append("u.birthday as birthDay, ");
				sqlCommand = sqlCommand.append("g.group_name as groupName, ");
				sqlCommand = sqlCommand.append("u.email as email, ");
				sqlCommand = sqlCommand.append("u.tel as tel, ");
				sqlCommand = sqlCommand.append("ja.name_level as nameLevel, ");
				sqlCommand = sqlCommand.append("duj.end_date as endDate, ");
				sqlCommand = sqlCommand.append("duj.total as total ");
				sqlCommand = sqlCommand.append("FROM tbl_user as u ");
				sqlCommand = sqlCommand.append("INNER JOIN mst_group as g ");
				sqlCommand = sqlCommand.append("ON u.group_id = g.group_id ");
				sqlCommand = sqlCommand
						.append("LEFT JOIN tbl_detail_user_japan duj ");
				sqlCommand = sqlCommand.append("ON u.user_id = duj.user_id ");
				sqlCommand = sqlCommand.append(" LEFT JOIN mst_japan AS ja ");
				sqlCommand = sqlCommand
						.append("ON duj.code_level = ja.code_level ");
				if (groupId > 0 && (fullName != null && fullName != "")) {
					sqlCommand = sqlCommand
							.append("WHERE u.full_name LIKE :name ");
					sqlCommand = sqlCommand.append("AND ");
					sqlCommand = sqlCommand.append("u.group_id = :gid ");

					if (sortType == null) {
						if (sortByFullName.equals("ASC")) {
							sqlCommand = sqlCommand
									.append("ORDER BY u.full_name ASC");
						}
						if (sortByFullName.equals("DESC")) {
							sqlCommand = sqlCommand
									.append("ORDER BY u.full_name desc");
						}
						if (sortByCodeLevel.equals("ASC")) {
							sqlCommand = sqlCommand
									.append(",duj.code_level ASC");
						}
						if (sortByCodeLevel.equals("DESC")) {
							sqlCommand = sqlCommand
									.append(",duj.code_level DESC");
						}
						if (sortByEndDate.equals("DESC")) {
							sqlCommand = sqlCommand
									.append(",duj.end_date DESC");
						}
						if (sortByEndDate.equals("ASC")) {
							sqlCommand = sqlCommand.append(",duj.end_date ASC");
						}
					} else {
						// begin logic sort by full name
						if ("sortByFullName".equals(sortType)) {
							if (sortByFullName.equals("DESC")) {
								sqlCommand = sqlCommand
										.append("ORDER BY u.full_name DESC");

								if (sortByCodeLevel.equals("DESC")) {
									sqlCommand = sqlCommand
											.append(",duj.code_level DESC");
								}
								if (sortByCodeLevel.equals("ASC")) {
									sqlCommand = sqlCommand
											.append(",duj.code_level ASC");
								}
								if (sortByEndDate.equals("DESC")) {
									sqlCommand = sqlCommand
											.append(",duj.end_date DESC");
								}
								if (sortByEndDate.equals("ASC")) {
									sqlCommand = sqlCommand
											.append(",duj.end_date ASC");
								}
							} else if (sortByFullName.equals("ASC")) {
								sqlCommand = sqlCommand
										.append("ORDER BY u.full_name ASC");

								if (sortByCodeLevel.equals("DESC")) {
									sqlCommand = sqlCommand
											.append(",duj.code_level DESC");
								}
								if (sortByCodeLevel.equals("ASC")) {
									sqlCommand = sqlCommand
											.append(",duj.code_level ASC");
								}
								if (sortByEndDate.equals("DESC")) {
									sqlCommand = sqlCommand
											.append(",duj.end_date DESC");
								}
								if (sortByEndDate.equals("ASC")) {
									sqlCommand = sqlCommand
											.append(",duj.end_date ASC");
								}
							}

						}
						// end logic sort by full name

						// begin logic sort by code level
						if ("sortByCodeLevel".equals(sortType)) {
							if (sortByCodeLevel.equals("DESC")) {
								sqlCommand = sqlCommand
										.append("ORDER BY duj.code_level DESC");

								if (sortByFullName.equals("ASC")) {
									sqlCommand = sqlCommand
											.append(",u.full_name ASC");
								}

								if (sortByFullName.equals("DESC")) {
									sqlCommand = sqlCommand
											.append(",u.full_name DESC");
								}
								if (sortByEndDate.equals("ASC")) {
									sqlCommand = sqlCommand
											.append(",duj.end_date ASC");
								}

								if (sortByEndDate.equals("DESC")) {
									sqlCommand = sqlCommand
											.append(",duj.end_date DESC");
								}

							} else if (sortByCodeLevel.equals("ASC")) {
								sqlCommand = sqlCommand
										.append("ORDER BY duj.code_level ASC");
								if (sortByFullName.equals("ASC")) {
									sqlCommand = sqlCommand
											.append(",u.full_name ASC");
								}

								if (sortByFullName.equals("DESC")) {
									sqlCommand = sqlCommand
											.append(",u.full_name DESC");
								}
								if (sortByEndDate.equals("ASC")) {
									sqlCommand = sqlCommand
											.append(",duj.end_date ASC");
								}

								if (sortByEndDate.equals("DESC")) {
									sqlCommand = sqlCommand
											.append(",duj.end_date DESC");
								}

							}
						}
						// end logic sort by code level

						// begin logic sort by end date
						if ("sortByEndDate".equals(sortType)) {
							if (sortByEndDate.equals("DESC")) {
								sqlCommand = sqlCommand
										.append("ORDER BY duj.end_date DESC");
								if (sortByFullName.equals("ASC")) {
									sqlCommand = sqlCommand
											.append(",u.full_name ASC");
								}

								if (sortByFullName.equals("DESC")) {
									sqlCommand = sqlCommand
											.append(",u.full_name DESC");
								}
								if (sortByCodeLevel.equals("ASC")) {
									sqlCommand = sqlCommand
											.append(",duj.code_level ASC");
								}

								if (sortByCodeLevel.equals("DESC")) {
									sqlCommand = sqlCommand
											.append(",duj.code_level DESC");
								}

							} else if (sortByEndDate.equals("ASC")) {
								sqlCommand = sqlCommand
										.append("ORDER BY duj.code_level ASC");
								if (sortByFullName.equals("ASC")) {
									sqlCommand = sqlCommand
											.append(",u.full_name ASC");
								}

								if (sortByFullName.equals("DESC")) {
									sqlCommand = sqlCommand
											.append(",u.full_name DESC");
								}
								if (sortByEndDate.equals("ASC")) {
									sqlCommand = sqlCommand
											.append(",duj.end_date ASC");
								}

								if (sortByEndDate.equals("DESC")) {
									sqlCommand = sqlCommand
											.append(",duj.end_date DESC");
								}

							}
						}
						// begin logic sort by end date
					}

				} else {
					if (fullName != null && fullName != "") {
						sqlCommand = sqlCommand
								.append("WHERE u.full_name LIKE :name ");
					}
					if (groupId > 0) {
						sqlCommand = sqlCommand
								.append("WHERE u.group_id = :gid ");
					}

					// begin logic sort by full name
					if ("sortByFullName".equals(sortType)) {
						if (sortByFullName.equals("DESC")) {
							sqlCommand = sqlCommand
									.append("ORDER BY u.full_name DESC");

							if (sortByCodeLevel.equals("DESC")) {
								sqlCommand = sqlCommand
										.append(",duj.code_level DESC");
							}
							if (sortByCodeLevel.equals("ASC")) {
								sqlCommand = sqlCommand
										.append(",duj.code_level ASC");
							}
							if (sortByEndDate.equals("DESC")) {
								sqlCommand = sqlCommand
										.append(",duj.end_date DESC");
							}
							if (sortByEndDate.equals("ASC")) {
								sqlCommand = sqlCommand
										.append(",duj.end_date ASC");
							}
						} else if (sortByFullName.equals("ASC")) {
							sqlCommand = sqlCommand
									.append("ORDER BY u.full_name ASC");

							if (sortByCodeLevel.equals("DESC")) {
								sqlCommand = sqlCommand
										.append(",duj.code_level DESC");
							}
							if (sortByCodeLevel.equals("ASC")) {
								sqlCommand = sqlCommand
										.append(",duj.code_level ASC");
							}
							if (sortByEndDate.equals("DESC")) {
								sqlCommand = sqlCommand
										.append(",duj.end_date DESC");
							}
							if (sortByEndDate.equals("ASC")) {
								sqlCommand = sqlCommand
										.append(",duj.end_date ASC");
							}
						}

					}
					// end logic sort by full name

					// begin logic sort by code level
					if ("sortByCodeLevel".equals(sortType)) {
						if (sortByCodeLevel.equals("DESC")) {
							sqlCommand = sqlCommand
									.append("ORDER BY duj.code_level DESC");

							if (sortByFullName.equals("ASC")) {
								sqlCommand = sqlCommand
										.append(",u.full_name ASC");
							}

							if (sortByFullName.equals("DESC")) {
								sqlCommand = sqlCommand
										.append(",u.full_name DESC");
							}
							if (sortByEndDate.equals("ASC")) {
								sqlCommand = sqlCommand
										.append(",duj.end_date ASC");
							}

							if (sortByEndDate.equals("DESC")) {
								sqlCommand = sqlCommand
										.append(",duj.end_date DESC");
							}

						} else if (sortByCodeLevel.equals("ASC")) {
							sqlCommand = sqlCommand
									.append("ORDER BY duj.code_level ASC");
							if (sortByFullName.equals("ASC")) {
								sqlCommand = sqlCommand
										.append(",u.full_name ASC");
							}

							if (sortByFullName.equals("DESC")) {
								sqlCommand = sqlCommand
										.append(",u.full_name DESC");
							}
							if (sortByEndDate.equals("ASC")) {
								sqlCommand = sqlCommand
										.append(",duj.end_date ASC");
							}

							if (sortByEndDate.equals("DESC")) {
								sqlCommand = sqlCommand
										.append(",duj.end_date DESC");
							}

						}
					}
					// end logic sort by code level

					// begin logic sort by end date
					if ("sortByEndDate".equals(sortType)) {
						if (sortByEndDate.equals("DESC")) {
							sqlCommand = sqlCommand
									.append("ORDER BY duj.end_date DESC");
							if (sortByFullName.equals("ASC")) {
								sqlCommand = sqlCommand
										.append(",u.full_name ASC");
							}

							if (sortByFullName.equals("DESC")) {
								sqlCommand = sqlCommand
										.append(",u.full_name DESC");
							}
							if (sortByCodeLevel.equals("ASC")) {
								sqlCommand = sqlCommand
										.append(",duj.code_level ASC");
							}

							if (sortByCodeLevel.equals("DESC")) {
								sqlCommand = sqlCommand
										.append(",duj.code_level DESC");
							}

						} else if (sortByEndDate.equals("ASC")) {
							sqlCommand = sqlCommand
									.append("ORDER BY duj.code_level ASC");
							if (sortByFullName.equals("ASC")) {
								sqlCommand = sqlCommand
										.append(",u.full_name ASC");
							}

							if (sortByFullName.equals("DESC")) {
								sqlCommand = sqlCommand
										.append(",u.full_name DESC");
							}
							if (sortByEndDate.equals("ASC")) {
								sqlCommand = sqlCommand
										.append(",duj.end_date ASC");
							}

							if (sortByEndDate.equals("DESC")) {
								sqlCommand = sqlCommand
										.append(",duj.end_date DESC");
							}

						}
					}
					// begin logic sort by end date
				}

				sqlCommand = sqlCommand.append(" LIMIT " + limit);
				sqlCommand = sqlCommand.append(" OFFSET " + offset);

				Query query = session.createSQLQuery(sqlCommand.toString())
						.addScalar("userId", Hibernate.INTEGER).addScalar(
								"fullName", Hibernate.STRING).addScalar(
								"birthDay", Hibernate.STRING).addScalar(
								"groupName", Hibernate.STRING).addScalar(
								"email", Hibernate.STRING).addScalar("tel",
								Hibernate.STRING).addScalar("nameLevel",
								Hibernate.STRING).addScalar("endDate",
								Hibernate.STRING).addScalar("total",
								Hibernate.INTEGER);

				if (groupId > 0 && (fullName != null && fullName != "")) {
					query.setString("name", "%" + fullName + "%");
					query.setInteger("gid", groupId);
				} else {
					if (fullName != null && fullName != "") {
						query.setString("name", "%" + fullName + "%");
					}
					if (groupId > 0) {
						query.setInteger("gid", groupId);
					}
				}
				query.setResultTransformer(Transformers
						.aliasToBean(UserDetailDto.class));

				return (List<UserDetailDto>) query.list();
			}
		};
		return (List<UserDetailDto>) getHibernateTemplate().execute(callback);
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see net.luvina.manageuser.dao.TblUserDao#getTotalRecords(int,
	 * java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	public int getTotalRecords(final int groupId, final String fullName) {
		HibernateCallback callback = new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException {
				StringBuilder sqlBuilder = new StringBuilder();
				sqlBuilder.append(" FROM TblUser u ");
				sqlBuilder.append(" INNER JOIN FETCH u.mstGroup g ");
				if (groupId > 0 && (fullName != null && fullName != "")) {
					sqlBuilder.append(" WHERE g.groupId=:groupId");
					sqlBuilder.append(" AND u.fullName LIKE :fullName");
				} else {
					if (fullName != null && fullName != "") {
						sqlBuilder.append(" WHERE u.fullName LIKE :fullName");
					}
					if (groupId > 0) {
						sqlBuilder.append(" WHERE g.groupId=:groupId");
					}
				}
				Query query = session.createQuery(sqlBuilder.toString());

				if (groupId > 0 && (fullName != null && fullName != "")) {
					query.setString("groupId", String.valueOf(groupId));
					query.setString("fullName", "%" + fullName + "%");
				} else {
					if ((fullName != null && fullName != "")) {
						query.setString("fullName", "%" + fullName + "%");
					}
					if (groupId > 0) {
						query.setString("groupId", String.valueOf(groupId));
					}
				}
				List<TblUser> userList = query.list();
				if (userList != null && userList.size() > 0) {
					return userList.size();
				} else {
					return 0;
				}
			}
		};
		return (Integer) getHibernateTemplate().execute(callback);
	}

	@SuppressWarnings("unchecked")
	public TblUser checkExistedLoginName(final int userId,
			final String loginName) {
		HibernateCallback callback = new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuilder sqlBuilder = new StringBuilder();
				sqlBuilder.append("FROM TblUser as u");
				sqlBuilder.append(" INNER JOIN FETCH u.mstGroup");
				if (userId == 0) {
					sqlBuilder.append(" WHERE u.loginName=:loginName");
				} else {
					sqlBuilder.append(" WHERE u.userId>:userId");
					sqlBuilder.append(" AND u.loginName=:loginName");
				}
				Query query = session.createQuery(sqlBuilder.toString());
				if (userId == 0) {
					query.setString("loginName", loginName);
				} else {
					query.setInteger("userId", userId);
					query.setString("loginName", loginName);
				}
				TblUser tblUser = (TblUser) query.uniqueResult();
				return tblUser;
			}

		};

		return (TblUser) getHibernateTemplate().execute(callback);
	}

	@SuppressWarnings("unchecked")
	public TblUser getUserByEmail(final int userId, final String email) {
		HibernateCallback callback = new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuilder sqlBuilder = new StringBuilder();
				sqlBuilder.append("FROM TblUser as u");
				sqlBuilder.append(" INNER JOIN FETCH u.mstGroup");
				if (userId == 0) {
					sqlBuilder.append(" WHERE u.email=:email");
				} else {
					sqlBuilder.append(" WHERE u.userId>:userId");
					sqlBuilder.append(" AND u.email=:email");
				}
				Query query = session.createQuery(sqlBuilder.toString());
				if (userId == 0) {
					query.setString("email", email);
				} else {
					query.setInteger("userId", userId);
					query.setString("email", email);
				}
				TblUser tblUser = (TblUser) query.uniqueResult();
				return tblUser;
			}
		};
		return (TblUser) getHibernateTemplate().execute(callback);
	}
}
